﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using InvestmentIntelligence.DB;
using InvestmentIntelligence.DbModel.CustomModels;
using InvestmentIntelligence.DbModel.Models;

namespace InvestmentIntelligence.Data.Repository.SqlImplementation
{
    public class AnalyticsIdeaRepository : ISqlAnalyticsIdeaRepository
    {
        private const int MergeTimeout = 300;
        private const string TableName = "[Analytics].[Idea]";
        
        private class TempObject
        {
            public Guid Id { get; set; }
        }

        public void Merge(MergeArea area, IEnumerable<AnalyticsIdea> ideas)
        {
            using (var conn = IIPConnectionString.CreateSqlConnection())
            {
                conn.Open();

                var tempTableName = DbHelper.BulkCopyToTempTable(conn, TableName, ideas);
                if (area.IdeaIds != null)
                {
                    MergeOverIdeas(conn, tempTableName, area.IdeaIds);
                }
                else
                {
                    MergeOverBook(conn, tempTableName, area.FundBookId);
                }
            }
        }

        private void MergeOverIdeas(SqlConnection con, string tempTableName, IEnumerable<Guid> ideas)
        {
            if (!ideas.Any())
            {
                throw new ArgumentException("No ideas given");
            }
            var ideasString = String.Join(",", ideas.Select(i => String.Format("'{0}'", i)));
            var searchCondition = String.Format("Id IN ({0})", ideasString);
            var sql = GetSqlCommand(searchCondition, tempTableName);
            var cmd = new SqlCommand(sql, con) {CommandTimeout = MergeTimeout};
            cmd.ExecuteNonQuery();
        }

        private void MergeOverBook(SqlConnection con, string tempTableName, int bookId)
        {
            var sql = GetSqlCommand("FundBookEntityId=@BookId", tempTableName);
            var cmd = new SqlCommand(sql, con) {CommandTimeout = MergeTimeout};
            cmd.Parameters.AddWithValue("@BookId", bookId);
            cmd.ExecuteNonQuery();
        }

        private string GetSqlCommand(string searchCondition, string tempTableName)
        {
            return string.Format(@"
WITH mergeArea AS (
        SELECT 
    idea.*
FROM
    {0} idea
WHERE
    {1}
)
MERGE
	mergeArea idea
USING
	{2} ideaTemp
ON
	idea.[Id] = ideaTemp.[Id]
WHEN MATCHED THEN 
	UPDATE SET idea.[FundBookEntityId] = ideaTemp.[FundBookEntityId], 
               idea.[Closed] = ideaTemp.[Closed],
               idea.[Type] = ideaTemp.[Type],
               idea.[Name] = ideaTemp.[Name],
               idea.[StartDate] = ideaTemp.[StartDate],
               idea.[EndDate] = ideaTemp.[EndDate]
WHEN NOT MATCHED THEN 
	INSERT ([Id],[FundBookEntityId],[Closed],[Type], [Name], [StartDate], [EndDate])
    VALUES ([Id],[FundBookEntityId],[Closed],[Type], [Name], [StartDate], [EndDate])
WHEN NOT MATCHED BY SOURCE THEN
	DELETE
OPTION (MAXDOP 1);
",
 TableName,
 searchCondition,
 tempTableName
 );
        }

        public void Insert(IEnumerable<AnalyticsIdea> ideas)
        {
            using (var conn = IIPConnectionString.CreateSqlConnection())
            {
                conn.Open();
                var bi = new BulkCopy<AnalyticsIdea>(conn, null, TableName, ideas);
                bi.Execute();
            }
        }

        public void DeleteIncluded(IEnumerable<Guid> ids, int fundBookId)
        {
            using (var conn = IIPConnectionString.CreateSqlConnection())
            {
                conn.Open();
                var tempTableName = DbHelper.BulkCopyToTempTable(conn, TableName, GetTempObjects(ids), "[Id]");
                ExecuteDeleteIncluded(conn, tempTableName, fundBookId);
            }
        }

        public void DeleteNotIncluded(IEnumerable<Guid> ids, int fundBookId)
        {
            using (var conn = IIPConnectionString.CreateSqlConnection())
            {
                conn.Open();
                var tempTableName = DbHelper.BulkCopyToTempTable(conn, TableName, GetTempObjects(ids), "[Id]");
                ExecuteDeleteNotIncluded(conn, tempTableName, fundBookId);
            }
        }

        private void ExecuteDeleteIncluded(SqlConnection con, string tempTableName, int fundBookId)
        {
            var sql = string.Format("DELETE FROM {0} WHERE [Id] IN (SELECT * FROM {1}) AND FundBookEntityId={2}", TableName, tempTableName, fundBookId);
            var cmd = new SqlCommand(sql, con) { CommandTimeout = MergeTimeout };
            cmd.ExecuteNonQuery();
        }

        private void ExecuteDeleteNotIncluded(SqlConnection con, string tempTableName, int fundBookId)
        {
            var sql = string.Format(
@"
WHILE EXISTS (SELECT NULL FROM {0} WHERE [Id] NOT IN (SELECT * FROM {1}) AND FundBookEntityId={2})
BEGIN
    DELETE TOP(500) FROM {0} WHERE [Id] NOT IN (SELECT * FROM {1}) AND FundBookEntityId={2}
END
", TableName, tempTableName, fundBookId);
            var cmd = new SqlCommand(sql, con) { CommandTimeout = MergeTimeout };
            cmd.ExecuteNonQuery();
        }

        private static IEnumerable<TempObject> GetTempObjects(IEnumerable<Guid> ids)
        {
            return ids.Select(i => new TempObject { Id = i });
        }

    }
}
